//
//  DBService.swift
//  FlameB
//
//  Created by 连俊 on 2020/8/3.
//  Copyright © 2020 连俊. All rights reserved.
//

import UIKit

let DBName = "securityFB.db"
let DBCipherKey = "COM.LIANJUN.FLAMEB.IOS"
let DBSQLUSERINFO = "SQLUSERINFO"
let SQLSTUCT_LOGIN = "SQLSTUCT_LOGIN"

///数据库基本操作
///一：登录用户管理
final class DBService: NSObject {
    static let shared = DBService()
    var dbQueue:FMDatabaseQueue!
    
    func openDB(with userId: String) {
        guard userId.count > 0 else {return}
        let paths = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)
        let MD5 = md5String(str: userId)
        guard let path = paths.first else {return}
        let documentsDirectory = "\(path)/\(MD5)"
        let fileManager = FileManager.default
        var isDir = ObjCBool(false)
        let isDirExist = fileManager.fileExists(atPath: documentsDirectory, isDirectory: &isDir)
        if !(isDirExist && isDir.boolValue) {
            try?fileManager.createDirectory(atPath: documentsDirectory, withIntermediateDirectories: true, attributes: nil)
            GTprint("\(documentsDirectory)")
        }
        let dbPath = "\(documentsDirectory)/\(DBName)"
        GTprint("dbpath---->\(dbPath)")
        
        dbQueue = FMDatabaseQueue.init(path: dbPath)
        if userId == "0000" {
            _ = createDBTables()
        } else  {
            _ = createDBTablesWithLogin()
        }
    }
       
    func createDBTables() -> Bool {
        var ret = false
        dbQueue.inDatabase { (db) in
            db.setKey(DBCipherKey)
            let sqlFile = Bundle.main.path(forResource: DBSQLUSERINFO, ofType: "txt")
            let sqls = try? String(contentsOfFile: sqlFile!, encoding: .utf8)
            guard let sqlArr = sqls?.components(separatedBy: "|") else {return}
            for sql in sqlArr {
                let retValue = ret == true ? 1 : 0
                let excValue = db.executeStatements(sql) == true ? 1 : 0
                ret = retValue & excValue == 0 ? false : true
            }
        }
        return ret
    }
    
    func createDBTablesWithLogin() -> Bool {
        var ret = false
        dbQueue.inDatabase { (db) in
            db.setKey(DBCipherKey)
            let sqlFile = Bundle.main.path(forResource: SQLSTUCT_LOGIN, ofType: "txt")
            let sqls = try? String(contentsOfFile: sqlFile!, encoding: .utf8)
            guard let sqlArr = sqls?.components(separatedBy: "|") else {return}
            for sql in sqlArr {
                let retValue = ret == true ? 1 : 0
                let excValue = db.executeStatements(sql) == true ? 1 : 0
                ret = retValue & excValue == 0 ? false : true
            }
        }
        return ret
    }
    
    func isTableExist(_ table: String) -> Bool {
        var ret = false
        dbQueue.inDatabase { (db) in
            db.setKey(DBCipherKey)
            ret = db.tableExists(table)
        }
        return ret
    }
    
    func createTable(name: String, sql: String) -> Bool {
        var ret = isTableExist(name)
        if !ret {
            dbQueue.inDatabase { (db) in
                db.setKey(DBCipherKey)
                ret = db.executeStatements(sql)
            }
        }
        return ret
    }
    
    func queryTotalWithSql(_ sql: String, _ params: [Any] = []) -> Int {
        var total = 0
        dbQueue.inTransaction { (db, rollBack) in
            db.setKey(DBCipherKey)
            guard let rs = db.executeQuery(sql, withArgumentsIn: params) else {return}
            if rs.next() {
                total = Int(rs.int(forColumnIndex: 0))
            }
            rs.close()
        }
        return total
    }
    
    func runSql(_ sql: String, _ params: [Any] = []) -> Bool {
        var ret = false
        if !ret {
            dbQueue.inDatabase { (db) in
                db.setKey(DBCipherKey)
                ret = db.executeUpdate(sql, withArgumentsIn: params)
            }
        }
        return ret
    }
}

///用户相关操作
extension DBService {
    func deleteShoppingTable(with userId: String) {
        dbQueue.inTransaction { (db, rollBack) in
            db.setKey(DBCipherKey)
            db.executeUpdate("DELETE FROM store_shoppingcart_sku", withArgumentsIn: [])
        }
    }
    
    func createShoppingTable() {
        _ = createTable(name: "store_shoppingcart_sku", sql: "CREATE TABLE IF NOT EXISTS store_shoppingcart_sku (skuId TEXT NOT NULL PRIMARY KEY UNIQUE ON CONFLICT REPLACE,skuCode TEXT,skuStatus INTEGER DEFAULT 1,skuName TEXT,productName TEXT,productId INTEGER DEFAULT 0,marketPrice INTEGER DEFAULT 0,addPrice INTEGER DEFAULT 0,price INTEGER DEFAULT 0,coverUrl TEXT,num INTEGER DEFAULT 1,stock INTEGER DEFAULT 999,isSelected bool DEFAULT FALSE,lastAddTime TEXT,brandId INTEGER,brandName TEXT,brandLogoUrl TEXT)")
    }
    
    func loadShopping(skuId: String) -> CartShoppingModel? {
        var retModel: CartShoppingModel?
        dbQueue.inTransaction { (db, rollBack) in
            db.setKey(DBCipherKey)
            guard let rs = db.executeQuery("SELECT skuId,skuCode,skuStatus,skuName,productName,productId,marketPrice,addPrice,price,coverUrl,num,stock,isSelected,lastAddTime,brandId,brandName,brandLogoUrl FROM store_shoppingcart_sku WHERE skuId = ?", withArgumentsIn: [skuId]) else {
                return
            }
            if rs.next(), let skuId = rs.string(forColumnIndex: 0), skuId.count > 0 {
                var columnIndex: Int32 = 0
                let shopping = CartShoppingModel()
                shopping.skuId = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                shopping.skuCode = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                shopping.skuStatus = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                shopping.skuName = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                shopping.productName = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                shopping.productId = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                shopping.marketPrice = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                shopping.addPrice = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                shopping.price = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                shopping.coverUrl = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                shopping.num = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                shopping.stock = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                shopping.isSelected = rs.bool(forColumnIndex: columnIndex); columnIndex = columnIndex + 1
                shopping.lastAddTime = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                shopping.brandId = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                shopping.brandName = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                shopping.brandLogoUrl = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                retModel = shopping
            }
        }
        return retModel
    }
    
    func loadAllShoppings() -> [CartShoppingModel] {
        var result = [CartShoppingModel]()
        dbQueue.inTransaction { (db, rollBack) in
            db.setKey(DBCipherKey)
            guard let rs = db.executeQuery("SELECT skuId,skuCode,skuStatus,skuName,productName,productId,marketPrice,addPrice,price,coverUrl,num,stock,isSelected,lastAddTime,brandId,brandName,brandLogoUrl FROM store_shoppingcart_sku ORDER BY lastAddTime DESC", withArgumentsIn: []) else {
                return
            }
            while rs.next() {
                let skuId = rs.string(forColumnIndex: 0) ?? ""
                if skuId.count > 0 {
                    var columnIndex: Int32 = 0
                    let shopping = CartShoppingModel()
                    shopping.skuId = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                    shopping.skuCode = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                    shopping.skuStatus = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                    shopping.skuName = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                    shopping.productName = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                    shopping.productId = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                    shopping.marketPrice = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                    shopping.addPrice = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                    shopping.price = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                    shopping.coverUrl = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                    shopping.num = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                    shopping.stock = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                    shopping.isSelected = rs.bool(forColumnIndex: columnIndex); columnIndex = columnIndex + 1
                    shopping.lastAddTime = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                    shopping.brandId = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                    shopping.brandName = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                    shopping.brandLogoUrl = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                    result.append(shopping)
                }
            }
        }
        return result
    }
    
    func addShopping(_ shopping: CartShoppingModel) {
        if shopping.skuId.count == 0 {
            return
        }
        updateShoppings([shopping])
    }
    
    func updateShoppings(_ shoppings: [CartShoppingModel]) {
        createShoppingTable()
        dbQueue.inTransaction { (db, rollBack) in
            db.setKey(DBCipherKey)
            if shoppings.count == 1 {
                db.shouldCacheStatements = true
            }
            for shopping in shoppings {
                db.executeUpdate("INSERT INTO store_shoppingcart_sku(skuId,skuCode,skuStatus,skuName,productName,productId,marketPrice,addPrice,price,coverUrl,num,stock,isSelected,lastAddTime,brandId,brandName,brandLogoUrl)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", withArgumentsIn: [shopping.skuId, shopping.skuCode, shopping.skuStatus, shopping.skuName, shopping.productName, shopping.productId, shopping.marketPrice, shopping.addPrice, shopping.price, shopping.coverUrl, shopping.num, shopping.stock, shopping.isSelected, shopping.lastAddTime, shopping.brandId, shopping.brandName, shopping.brandLogoUrl])
            }
        }
    }
    
    func updateShopping(_ shopping: CartShoppingModel) {
        updateShoppings([shopping])
    }
    
    func deleteShoppings(skuIds: [String]) {
        dbQueue.inTransaction { (db, rollBack) in
            db.setKey(DBCipherKey)
            for skuId in skuIds {
                db.executeUpdate("DELETE FROM store_shoppingcart_sku WHERE skuId = ?", withArgumentsIn: [skuId])
            }
        }
    }
    
    func deleteShopping(skuId: String) {
        deleteShoppings(skuIds: [skuId])
    }
    
    func queryShoppingTotal() -> Int {
        return queryTotalWithSql("SELECT count(*) FROM store_shoppingcart_sku")
    }
    
    func userLoginAction(_ telNum: String, pwd: String, completion: @escaping (LoginStatus) -> Void) {
        var isRegisterUser: Bool = false
        var loginResult: Bool = false
        let results = loadAllShoppings()
        for result in results {
            if result.skuId == telNum {
                isRegisterUser = true
                if result.skuCode == pwd {
                    loginResult = true
                } else {
                    loginResult = false
                }
            }
        }
        if isRegisterUser {
            if loginResult {
                completion(LoginStatus.success)
            } else {
                completion(LoginStatus.failure)
            }
        } else {
            let model = CartShoppingModel()
            model.skuId = telNum
            model.skuCode = pwd
            model.skuStatus = 0
            model.skuName = "匿名用户\(results.count+1)"
            model.productName = ""
            model.productId = 0
            model.marketPrice = 0
            model.addPrice = 0
            model.price = 0
            model.coverUrl = ""
            model.num = 0
            model.stock = 0
            model.isSelected = true
            model.lastAddTime = ""
            model.brandId = 0
            model.brandName = ""
            model.brandLogoUrl = ""
            addShopping(model)
            completion(LoginStatus.registerSuccess)
        }
    }
}

///录入操作
extension DBService {
    func createIMBrandsTable() {
        _ = createTable(name: "store_im_brands", sql: "CREATE TABLE IF NOT EXISTS store_im_brands (brandId INTEGER NOT NULL PRIMARY KEY UNIQUE ON CONFLICT REPLACE,brandName TEXT,brandLogoUrl TEXT,unreadMsgCount INTEGER DEFAULT 0,lastMsgTime TEXT,lastMsg TEXT,totalTime INTEGER DEFAULT 0)")
    }
    
    func loadAllBrands() -> [StoreIMBrand] {
        var result = [StoreIMBrand]()
        dbQueue.inTransaction { (db, rollBack) in
            db.setKey(DBCipherKey)
            guard let rs = db.executeQuery("SELECT brandId,brandName,brandLogoUrl,unreadMsgCount,lastMsgTime,lastMsg,totalTime FROM store_im_brands ORDER BY unreadMsgCount ASC", withArgumentsIn: []) else {
                return
            }
            while rs.next() {
                let brandId = Int(rs.int(forColumnIndex: 0))
                if brandId > 0 {
                    var columnIndex: Int32 = 0
                    let brand = StoreIMBrand()
                    brand.brandId = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                    brand.brandName = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                    brand.brandLogoUrl = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                    brand.unreadMsgCount = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                    brand.lastMsgTime = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                    brand.lastMsg = rs.string(forColumnIndex: columnIndex) ?? ""; columnIndex = columnIndex + 1
                    brand.totalTime = Int(rs.int(forColumnIndex: columnIndex)); columnIndex = columnIndex + 1
                    result.append(brand)
                }
            }
        }
        return result
    }
    
    func addBrand(_ model: StoreIMBrand) {
        let tmpmodel = model
        if let lastModel = lastBrand() {
            tmpmodel.brandId = lastModel.brandId + 1
            tmpmodel.unreadMsgCount = lastModel.unreadMsgCount + 1
        } else {
            tmpmodel.brandId = 1
            tmpmodel.unreadMsgCount = 1
        }
        updateBrands([tmpmodel])
    }
    
    func updateBrands(_ models: [StoreIMBrand]) {
        createIMBrandsTable()
        dbQueue.inTransaction { (db, rollBack) in
            db.setKey(DBCipherKey)
            if models.count == 1 {
                db.shouldCacheStatements = true
            }
            for model in models {
                db.executeUpdate("INSERT INTO store_im_brands(brandId,brandName,brandLogoUrl,unreadMsgCount,lastMsgTime,lastMsg,totalTime)VALUES(?,?,?,?,?,?,?)", withArgumentsIn: [model.brandId, model.brandName,model.brandLogoUrl,model.unreadMsgCount,model.lastMsgTime,model.lastMsg,model.totalTime])
            }
        }
    }
    
    func lastBrand() -> StoreIMBrand? {
        let array = loadAllBrands()
        if array.count == 0 {
            return nil
        } else {
            let model = array.last
            return model
        }
    }
}

extension DBService {
    func md5String(str:String) -> String {
        let cStr = str.cString(using: .utf8)
        let buffer = UnsafeMutablePointer<UInt8>.allocate(capacity: 16)
        CC_MD5(cStr,(CC_LONG)(strlen(cStr!)), buffer)
        var md5String = ""
        for idx in 0 ... 15 {
            let obcStrl = String(format: "%02x", buffer[idx])
            md5String.append(obcStrl)
        }
        free(buffer)
        return md5String
    }
}
